use skyEdu
GO

create table sys_course
(
	cou_id int identity(1,1) primary key not null,
	cou_year char(4),				     --年度
	cou_term nvarchar(30),				 --学期
	cou_subject nvarchar(30),			 --科目
	cou_grade nvarchar(30),				 --年级
	cou_claType nvarchar(30),			 --班级类型
	cou_claLevel nvarchar(30),			 --课程层次
	cou_beginTime nvarchar(11),          --开学时间
	cou_endTime nvarchar(11),			 --结束时间
	cou_classNumber int,		 --上课次数
	cou_classHour decimal(6,2),		 --课时数 1.25
	cou_perunitFee int,		 --单课次费用 120
	--cou_totalUnitFee int not null,       --总课时费用 = 上课次数*单课次费用
	cou_predictNum int,					 --预招人数 20
	cou_bookFee decimal(8,2),					 --书本费  100.00
	cou_bookVersion nvarchar(300),				 --教材版本	
	cou_classTime decimal(6,1),		--上课时间
	
	cou_createTime datetime2(0)		 --创建时间
)
drop table sys_course;

--Course Outline 课程大纲表
create table sys_outline
(
	out_id int identity(1,1) primary key not null,
	out_cou_id int ,
	out_claContent nvarchar(1000),				--上课内容
	out_teaAims nvarchar(1000),					--教学目的
	out_knowledge nvarchar(1000),				--主要知识点
	out_difficulty int,							--难易程度  5最难 4难 3一般 2容易 1较容易 0未选择
	out_count int        --课次数
)
drop table sys_outline;
select * from sys_outline;
insert into sys_outline(out_cou_id,out_claContent,out_teaAims,out_knowledge,out_difficulty)
values()
delete from sys_outline where out_cou_id=3 --out_count>33;
delete from sys_course where cou_id=
select COUNT(out_count) from sys_outline where out_cou_id=1;

insert into sys_course(cou_year,cou_term,cou_subject,cou_grade,cou_claType,
cou_claLevel,cou_createTime,cou_beginTime,cou_endTime,cou_classNumber,
cou_classHour,cou_perunitFee,cou_predictNum,cou_bookFee,cou_bookVersion,
cou_claContent,cou_teaAims,cou_knowledge,cou_difficulty)
values('2012','寒假','英语','六年级','精英班','精英',GETDATE(),'2012-01-01','2012-01-30',18,2.00,120,20,100.00,'人教版');

select cou_id,cou_classNumber,cou_year,cou_term,cou_subject,
cou_grade,cou_claLevel,cou_claType,cou_bookVersion
from sys_course
order by cou_createTime desc;


update sys_course set cou_difficulty=2 where cou_id=2; 
delete from sys_course where cou_id in (6,7)
select CourseType,CourseCate from Edu_Course where Status=1;


select * from v_CourseList s where s.课程状态=1;

select u.UserCName,s.StudentName from Edu_Student s ,Users u 
where s.HomeroomId = u.User_ID group by u.UserCName,s.StudentName